package com.rains.excel;

import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.lang.reflect.Method;
import java.text.ParseException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.dom4j.Attribute;
import org.dom4j.Document;
import org.dom4j.Element;
import org.dom4j.io.SAXReader;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

/**
 * 根据xml模板从excel中读取并实例化对象
 * @author rains
 *
 */
public class ReadObjectFormExcelByXml {
    private static final Logger logger = LoggerFactory.getLogger(ReadObjectFormExcelByXml.class);
    
    /**
     * 根据输入流和模板xml返回map数据对象
     * @param inputStreamFileName excel输入流(2003版本)
     * @param template xml模板文件
     * @return Map<String, List>, String是sheet名称,List是bean对象的属性list
     * @throws FileNotFoundException
     * @throws IOException
     */
    @SuppressWarnings({ "rawtypes", "unchecked" })
    public static Map<String, List> getExcelMap(InputStream inputStreamFileName, String template)
            throws FileNotFoundException, IOException {
        POIFSFileSystem fs = new POIFSFileSystem(inputStreamFileName);
        HSSFWorkbook workbook = new HSSFWorkbook(fs);

        SAXReader reader = new SAXReader();
        Document document = null;
        try {
            document = reader.read(Thread.currentThread().getContextClassLoader().getResource(template));
        } catch (Exception e) {
            logger.error("ReadObjectFormExcelByXml#getExcelMap error:" + e.getMessage());
        }
        Element root = document.getRootElement();
        Map<String, List> dataMap = new HashMap<String, List>();
        for (Iterator i = root.elementIterator(); i.hasNext();) {
            List dataList = new ArrayList();
            Element element = (Element) i.next();

            String sheetname = element.attribute("name").getValue();

            String fromRowStr = getAttributeValue(element, "fromRow");
            String toRowStr = getAttributeValue(element, "toRow");
            String checkEndColStr = getAttributeValue(element, "checkEndCol");

            int fromRow = -1;
            int toRow = 5000;
            short checkEndCol = -1;

            try {
                fromRow = Integer.parseInt(fromRowStr);
            } catch (Exception e) {
                logger.error("ReadObjectFormExcelByXml#getExcelMap error:" + e.getMessage());
            }
            try {
                if (toRowStr != null && toRowStr.trim().length() > 0) {
                    toRow = Integer.parseInt(toRowStr);
                }
            } catch (Exception e) {
                logger.error("ReadObjectFormExcelByXml#getExcelMap error:" + e.getMessage());
            }
            try {
                checkEndCol = Short.parseShort(checkEndColStr);
            } catch (Exception e) {
                logger.error("ReadObjectFormExcelByXml#getExcelMap error:" + e.getMessage());
            }
            if (fromRow < 0) {
                throw new RuntimeException("ReadObjectFormExcelByXml#getExcelMap fromRow should be a valid positive number");
            }
            if (toRow < 0) {
                throw new RuntimeException("ReadObjectFormExcelByXml#getExcelMap toRow should be a valid positive number");
            }
            if (checkEndCol < 0) {
                throw new RuntimeException("ReadObjectFormExcelByXml#getExcelMap checkEndCol should be a valid positive number");
            }

            try {
                // loop start here!!!
                for (int row = fromRow; row <= toRow; row++) {
                    Object object = null;
                    object = getCellValue(sheetname, row, checkEndCol, null, workbook);
                    if (object == null || "".equals(object.toString())) {
                        break;
                    }
                    for (Iterator it = element.elementIterator(); it.hasNext();) {
                        Element objElement = (Element) it.next();
                        String classAttVal = getAttributeValue(objElement, "class");
                        Class clazz = Class.forName(classAttVal);
                        object = clazz.newInstance();
                        for (Iterator cit = objElement.elementIterator(); cit.hasNext();) {
                            Element cellElement = (Element) cit.next();
                            String refclsmethodVal = getAttributeValue(cellElement, "refclsmethod");
                            if (refclsmethodVal != null) {
                                
                            } else {
                                String field = cellElement.element("objAttr").getStringValue();
                                String col = cellElement.element("col").getStringValue();
                                Short c = Short.parseShort(col);
                                String datatype = cellElement.element("datatype").getStringValue();
                                String methodName = "set" + field.substring(0, 1).toUpperCase() + field.substring(1);
                                Method m = clazz.getMethod(methodName, Class.forName(datatype));
                                Object v = getCellValue(sheetname, row, c, datatype, workbook);
                                logger.debug("****【0】load xls:" + sheetname + "/column:" + c + "/field:" + field
                                        + "/newValue:" + (v == null ? "null" : v.toString()));
                                m.invoke(object, v);
                            }
                        }
                    }
                    dataList.add(object);
                } // row loop
            } catch (Exception e) {
                logger.error("ReadObjectFormExcelByXml#getExcelMap error:" + e.getMessage());
                throw new RuntimeException(e.getMessage());
            }
            dataMap.put(sheetname, dataList);
        }
        return dataMap;
    }
    
    private static String getAttributeValue(Element element, String attrName) {
        String attrVal = null;

        Attribute attr = element.attribute(attrName);

        if (attr != null) {
            attrVal = attr.getValue();
        }

        return attrVal;
    }

    private static Object getCellValue(String sheetname, final int row, int col, String dataType, HSSFWorkbook workbook)
            throws ClassNotFoundException, InstantiationException, IllegalAccessException, ParseException {

        Object result = null;

        HSSFSheet sheet = workbook.getSheet(sheetname);
        if (sheet == null) {
            return null;
        }
        HSSFRow hrow = sheet.getRow(row);
        if (hrow == null) {
            return null;
        }
        HSSFCell cell = hrow.getCell(col);
        if (cell == null) {
            return null;
        }

        switch (cell.getCellType()) {
        case HSSFCell.CELL_TYPE_STRING:
            result = cell.getStringCellValue().trim();

            if (dataType != null && dataType.indexOf("Date") >= 0) {

            } else if (dataType != null && dataType.indexOf("Double") >= 0) {
                if (result == null || "".equals(result)) {
                    result = Double.valueOf(0.0D);
                    ;
                } else {
                    result = Double.valueOf(result.toString());
                }
            } else if (dataType != null && dataType.indexOf("Long") >= 0) {
                if (result == null || "".equals(result)) {
                    result = Long.valueOf(0L);
                } else {
                    result = Long.valueOf(result.toString());
                }
            } else if (dataType != null && dataType.indexOf("Integer") >= 0) {
                if (result == null || "".equals(result)) {
                    result = Integer.valueOf(0);
                } else {
                    result = Integer.valueOf(result.toString());
                }
            }
            break;
        case HSSFCell.CELL_TYPE_NUMERIC:
            double res = cell.getNumericCellValue();
            if (dataType == null) {
                result = res;
            } else if (dataType.indexOf("String") >= 0) {
                String tmp = String.valueOf(res);
                if (tmp.endsWith(".0"))
                    tmp = tmp.substring(0, tmp.indexOf(".0"));

                result = tmp;
            } else if (dataType.indexOf("Long") >= 0) {
                result = Long.valueOf(Math.round(res));
            } else if (dataType.indexOf("Integer") >= 0) {
                result = Integer.valueOf(Math.round(res) + "");

            } else if (dataType.indexOf("Short") >= 0) {
                result = Math.round(res);
            } else if (dataType != null && dataType.indexOf("Date") >= 0) {
            } else {
                result = res;
            }
            break;
        case HSSFCell.CELL_TYPE_FORMULA:
            cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
            result = cell.getNumericCellValue();
            if ("NaN".equals(result.toString())) {
                result = null;
            }
            break;
        case HSSFCell.CELL_TYPE_BLANK:
            result = null;
            break;
        case HSSFCell.CELL_TYPE_BOOLEAN:
            break;
        case HSSFCell.CELL_TYPE_ERROR:
            break;
        default:
            break;
        }
        logger.debug("###res:" + result + "###/row:" + row + "###c:" + col + "###/" + dataType);
        return result;
    }
}
